/*
Run this script on:

        mssql-tl.db.jwan.it,14444.TTTLines2013    -  This database will be modified

to synchronize it with:

        kalgan01.cs.jwan.it,1443.Jambo2014_sviluppo

You are recommended to back up your database before running this script

Script created by SQL Compare version 9.0.0 from Red Gate Software Ltd at 02/04/2014 12:27:49

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Creating [dbo].[v_jb_clienti]'
GO

-- ===========================================================================================
-- Author:		Andrea Marchesini
-- Create date: 2012-09-17
-- Description:	Restituisci tutti i clienti JB dall'unione dei clienti Jambo con quelli remoti
-- ============================================================================================
CREATE VIEW [dbo].[v_jb_clienti]

--WITH ENCRYPTION
 WITH ENCRYPTION AS
 

SELECT     	
	IDCLIENTE,
	(select dbo.fn_jb_compagnia()) as CODCOMP,
	---1 as IDCLIENTE_REMOTE,
	COGNOME,
	NOME,
	INDIRIZZO,
	CITTA,
	TELEFONO1 AS TELEFONO,
	EMAIL,
	CODICEFISCALE,
	PIVA,
	DATA_NASCITA,
	LUOGO_NASCITA,
	NAZIONE,
	NOTE
FROM  dbo.clienti 
UNION
SELECT 

	--cast(codcomp+IDCLIENTE_REMOTE as nvarchar(50)) as JB_IDCLIENTE,
	---1 as IDCLIENTE,
	IDCLIENTE_REMOTE,
	CODCOMP,
	COGNOME,
	NOME,
	INDIRIZZO,
	CITTA,
	TELEFONO,
	EMAIL,
	CODICEFISCALE,
	PIVA,
	DATA_NASCITA,
	LUOGO_NASCITA,
	NAZIONE,
	NOTE
FROM dbo.jb_remote_clienti


GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO
